package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select sum(amount) from order_info where date(create_time) = '2023-01-06'")
    Double getList1();

    @Select("call pro1()")
    List<Vo> getList2();

    @Select("select\n" +
            "    bo.org_name `name`,\n" +
            "    sum(tt.actual_distance) `distance`,\n" +
            "    count(tt.id) `num`,\n" +
            "    sum(timestampdiff(second ,tt.actual_start_time,tt.actual_end_time)) `time`,\n" +
            "    sum(timestampdiff(second ,tt.actual_start_time,tt.actual_end_time))/count(tt.id) `avgTime`,\n" +
            "    sum(tt.actual_distance)/count(tt.id) `avgDistancce`,\n" +
            "    rank() over (order by count(tt.id) desc) pm\n" +
            "from transport_task tt\n" +
            "left join line_base_info lbi on tt.line_id = lbi.id\n" +
            "left join base_organ bo on lbi.org_id = bo.id\n" +
            "group by bo.org_name")
    List<Vo1> getList3();

    @Select("select\n" +
            "    bri2.name,\n" +
            "    count(ttd.id) `value`\n" +
            "from transport_task_detail ttd\n" +
            "left join transport_task tt on tt.id = ttd.transport_task_id\n" +
            "left join line_base_info lbi on tt.line_id = lbi.id\n" +
            "left join base_organ bo on lbi.org_id = bo.id\n" +
            "left join base_region_info bri on bo.region_id = bri.id\n" +
            "left join base_region_info bri2 on bri.parent_id = bri2.id\n" +
            "group by bri2.name")
    List<Vo> getList4();
}
